package cn.sfa.service.manage;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.rowset.serial.SerialException;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.support.AbstractLobCreatingPreparedStatementCallback;
import org.springframework.jdbc.support.lob.LobCreator;
import org.springframework.jdbc.support.lob.DefaultLobHandler;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import cn.sfa.cache.CacheFactory;
import cn.sfa.cache.ICacheHelper;
import cn.sfa.common.pagination.Pagination;
import cn.sfa.common.utils.CommonUtil;
import cn.sfa.common.utils.MajorKeyGenerate;
import cn.sfa.common.utils.TableNameEnum;
import cn.sfa.entity.meeting.SaleMeeting;
import cn.sfa.entity.news.SaleNews;
import cn.sfa.entity.news.SaleNewsBranch;
import cn.sfa.service.meeting.MeetingService;


/**
 * 早会管理服务类
 * @ClassName：ManageMeetingService 
 * @Description：TODO
 * @author：LiXiang
 * @date：2012-4-20 上午09:54:40 
 *
 */
@Service
@Transactional
public class ManageMeetingService {
	
	Logger logger = LoggerFactory.getLogger(MeetingService.class);
	ICacheHelper  cacheHelper = CacheFactory.CreateCacheHelper();
	
	@Autowired
	NamedParameterJdbcTemplate jdbcTemplate;
	
	@Autowired
	MajorKeyGenerate majorKeyGenerate;
	@Autowired
	private DefaultLobHandler lobHandler; 



	/**
	 * 添加早会
	 * @param saleNews
	 * @return boolean
	 * @throws SQLException 
	 * @throws SerialException 
	 */
	public boolean addMeeting(final SaleMeeting saleMeeting,final String isInner)
	{	
		String sql="insert into SALEMEETING("+
		"SALEMEETINGID,CREATEDATE,CREATEUSER,UPDATEDATE,UPDATEUSER,"+
		"BRANCHID,AGENTLEVEL,ISAFFIX,MEETINGCOUNT,MEETINGTITLE,"+
		"MEETINGCONTENT,MEETINGPROCESS,MEETINGMASTER,MEETINGSTATUS,STARTDATE,"+
		"ENDDATE,VCOL1)"+
		" values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		
		int count=	jdbcTemplate.getJdbcOperations().execute(
				sql,
				new AbstractLobCreatingPreparedStatementCallback(
						this.lobHandler) {
					protected void setValues(PreparedStatement ps,
							LobCreator lobCreator) throws SQLException,
							DataAccessException {
						String id=majorKeyGenerate.keyGenerate(TableNameEnum.SALEMEETING);
						ps.setString(1, id);
						saleMeeting.setSaleMeetingId(id);
						ps.setTimestamp(2, new Timestamp(saleMeeting.getCreateDate().getTime()));
						ps.setString(3, CommonUtil.jugeAndFixNull(saleMeeting.getCreateUid()));
						ps.setTimestamp(4, new Timestamp(saleMeeting.getUpdateDate().getTime()));
						ps.setString(5, CommonUtil.jugeAndFixNull(saleMeeting.getUpdateId()));
						ps.setString(6, CommonUtil.jugeAndFixNull(saleMeeting.getBranchId()));
						ps.setString(7, CommonUtil.jugeAndFixNull(saleMeeting.getAgentLevel()));
						ps.setString(8, CommonUtil.jugeAndFixNull(saleMeeting.getIsAffix()));
						ps.setInt(9, saleMeeting.getMeetingCount());
						ps.setString(10,CommonUtil.jugeAndFixNull(saleMeeting.getMeetingTitle()));
						
						lobCreator.setClobAsString(ps, 11,saleMeeting.getMeetingContent()); // 设置详细的CLOB字段的内容
						
						ps.setString(12, CommonUtil.jugeAndFixNull(saleMeeting.getMeetingProcess()));
						ps.setString(13,CommonUtil.jugeAndFixNull(saleMeeting.getMeetingMaster()));
						ps.setString(14, CommonUtil.jugeAndFixNull(saleMeeting.getMeetingStatus()));
						
						
						ps.setTimestamp(15,new Timestamp(saleMeeting.getStartDate().getTime()));
						ps.setTimestamp(16, new Timestamp(saleMeeting.getEndDate().getTime()));
						
						ps.setString(17, CommonUtil.jugeAndFixNull(isInner)); //设置是否内勤新增的早会
						 
						
					}
				});
			
		
		if(count>0)
		{
			 
			return true;
		}
		return false;
	}
	
	/**
	 * 查询出所有早会
	 * @return List<SaleMeeting>
	 */
	public List<SaleMeeting> getMeetings()
	{
		String sql="select SALEMEETINGID,CREATEDATE,CREATEUSER,UPDATEDATE,UPDATEUSER,"+
		"BRANCHID,AGENTLEVEL,ISAFFIX,MEETINGCOUNT,MEETINGTITLE,"+
		"MEETINGCONTENT,MEETINGPROCESS,MEETINGMASTER,MEETINGSTATUS,STARTDATE,"+
		"ENDDATE from SALEMEETing order by CREATEDATE DESC";
		List<SaleMeeting> news=new ArrayList<SaleMeeting>();
		news=jdbcTemplate.query(sql,new HashMap<String,Object>(),new BeanPropertyRowMapper<SaleMeeting>(SaleMeeting.class));
		return news;
	}
	
	/**
	 * 分页查询
	 */
	public Pagination searchMeetings(
			int currentPage,//页码
			int numPerPage,//页尺寸
			String url,//分页url
			SaleMeeting saleMeeting,
			String beginDate,
			String endDate){ 
		
		StringBuilder sqlStr = new StringBuilder();
		sqlStr.append("select * from table(pkg_sfa_salemeeting.searchMeetingsForBack(:title,:content,:master,:startDate,:endDate,:status)) "); 
	
		Map<String,Object> paramMap = new HashMap<String,Object>(); 

			paramMap.put("title", saleMeeting.getMeetingTitle());

			paramMap.put("content", saleMeeting.getMeetingContent());

			paramMap.put("master", saleMeeting.getMeetingMaster());

			paramMap.put("startDate",beginDate);

			paramMap.put("endDate",endDate);

			paramMap.put("status",saleMeeting.getMeetingStatus());

				 
		return new Pagination(sqlStr.toString(),paramMap,currentPage,numPerPage,url,jdbcTemplate); 
  
	}

	public boolean deleteMeetingById(String saleMeetingId) {
		String sql="delete salemeeting where salemeetingid=:saleMeetingId";
		Map<String,Object> map=new HashMap<String,Object>();
		map.put("saleMeetingId", saleMeetingId);
		int count=jdbcTemplate.update(sql, map);
		if(count>0)
		{
			return true;
		}
		return false;
	}

	/**
	 * TODO
	 * @author: LiXiang
	 * @param saleMeetingId
	 * @return
	 */
	public SaleMeeting getMeeting(String saleMeetingId) {
		String sql = "select sns.SALEMEETINGID,sns.CREATEDATE,sns.CREATEUSER,sns.UPDATEDATE,sns.UPDATEUSER,"+
		"sns.BRANCHID,sns.AGENTLEVEL,sns.ISAFFIX,sns.MEETINGCOUNT,sns.MEETINGTITLE,"+
		"sns.MEETINGCONTENT,sns.MEETINGPROCESS,sns.MEETINGMASTER,sns.MEETINGSTATUS,sns.STARTDATE,"+
		"sns.ENDDATE,su1.username mastername"+
		" from salemeeting sns,SALEUSERSLOGIN su1 where salemeetingid=:saleMeetingId and su1.saleusersloginid=sns.meetingmaster";
		final SaleMeeting meeting=new SaleMeeting();
		Map<String,Object> paramMap = new HashMap<String,Object>();
		paramMap.put("saleMeetingId",saleMeetingId);
		jdbcTemplate.query(sql,paramMap ,new RowCallbackHandler() {
					
					@Override
					public void processRow(ResultSet rs) throws SQLException {
						
						meeting.setSaleMeetingId(rs.getString("SALEMEETINGID"));
						meeting.setCreateDate(rs.getTimestamp("createDate"));
						meeting.setCreateUid(rs.getString("createuser"));
						meeting.setUpdateDate(rs.getTimestamp("updatedate"));
						meeting.setUpdateId(rs.getString("updateuser"));
						meeting.setAgentLevel(rs.getString("agentLevel"));
						meeting.setBranchId(rs.getString("branchid"));
						meeting.setIsAffix(rs.getString("isAffix"));
						meeting.setMeetingCount(rs.getInt("meetingcount"));
						meeting.setMeetingTitle(rs.getString("meetingtitle"));
						
						String clobText = lobHandler.getClobAsString(rs,"meetingcontent");
						meeting.setMeetingContent(clobText);
						meeting.setMeetingProcess(rs.getString("meetingprocess"));
						meeting.setMeetingMaster(rs.getString("meetingmaster"));
						meeting.setMeetingStatus(rs.getString("meetingstatus"));
						meeting.setStartDate(rs.getTimestamp("startdate"));
						meeting.setEndDate(rs.getTimestamp("enddate"));
						meeting.setMasterName(rs.getString("mastername"));
					}
						
		});
		
		
		return meeting;
	}
	
	/**
	 * 更改审核状态
	 * @param saleNewsBranchId
	 * @return
	 */
	public boolean updateMeetingStatus(final String saleMeetingId,final String meetingStatus)
	{	
		String sql="update SALEMEETING set meetingstatus=? where salemeetingid=?";
		
		int count=	jdbcTemplate.getJdbcOperations().execute(
				sql,
				new AbstractLobCreatingPreparedStatementCallback(
						this.lobHandler) {
					protected void setValues(PreparedStatement ps,
							LobCreator lobCreator) throws SQLException,
							DataAccessException {
						ps.setString(1, CommonUtil.jugeAndFixNull(meetingStatus));
						ps.setString(2, CommonUtil.jugeAndFixNull(saleMeetingId));
						
					}
				});

		if(count>0)
		{
			return true;
		}
		return false;
	}
}
